# import sys
# sys.path.append('E:\\gitee\\mp_alwaysup_fastapi')

from process import db_connect
import json
from datetime import date, datetime, timedelta
from decimal import Decimal
from pandas import Timestamp
from process import tushareFunc as Tushare
from typing import List, Dict, Any, Literal, Optional
import pandas as pd
import os


class CustomEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, (date, datetime)):
            return obj.isoformat()  # 将日期对象转换为 ISO 格式字符串
        elif isinstance(obj, Timestamp):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(obj, Decimal):
            return str(obj)  # 将 Decimal 对象转换为字符串
        else:
            return super().default(obj)

# 把20240920 转换为2024-09-21


def format_date(date_str) -> date:
    if len(date_str) == 10 and date_str[4] == '-' and date_str[7] == '-':
        return datetime.strptime(date_str, "%Y-%m-%d").date()
    return datetime.strptime(f"{date_str[:4]}-{date_str[4:6]}-{date_str[6:]}", "%Y-%m-%d").date()

# 计算给定日期减去指定天数后的日期


def calculate_date_minus_days(date_string, days_to_subtract):
    date = datetime.strptime(date_string, '%Y%m%d').date()
    new_date = date - timedelta(days=days_to_subtract)
    return new_date

# 示例收盘时间判断函数


def is_market_close_time(time):
    # 这里你可以定义市场收盘时间
    # 示例：假设市场收盘时间为16:00
    return time.hour > 15

# 根据当前时间判断最新的收盘日期


def find_last_trading_day2(current_date, current_time):
    # 检查今天是否为交易日
    if Tushare.tushare.is_trading_day(current_date.strftime("%Y%m%d")):
        # 如果今天是交易日，检查当前时间是否为收盘时间
        if is_market_close_time(current_time):
            return current_date
        else:
            # 如果不是收盘时间，则查找前一天是否为交易日
            while True:
                previous_date = current_date - timedelta(days=1)
                if Tushare.tushare.is_trading_day(previous_date.strftime("%Y%m%d")):
                    return previous_date
                current_date = previous_date
    else:
        # 如果今天不是交易日，查找前一天的交易日
        while True:
            previous_date = current_date - timedelta(days=1)
            if Tushare.tushare.is_trading_day(previous_date.strftime("%Y%m%d")):
                return previous_date
            current_date = previous_date


def find_last_trading_day1(current_date, current_time):
    # 检查今天是否为交易日
    if Tushare.tushare.is_trading_day(current_date.strftime("%Y%m%d")):
        # 如果今天是交易日，检查当前时间是否为收盘时间
        if is_market_close_time(current_time):
            return current_date
        else:
            # 如果不是收盘时间，则查找前一天是否为交易日
            previous_date = current_date - timedelta(days=1)
            while not Tushare.tushare.is_trading_day(previous_date.strftime("%Y%m%d")):
                previous_date -= timedelta(days=1)
            return previous_date
    else:
        # 如果今天不是交易日，查找前一天的交易日
        previous_date = current_date - timedelta(days=1)
        while not Tushare.tushare.is_trading_day(previous_date.strftime("%Y%m%d")):
            previous_date -= timedelta(days=1)
        return previous_date


def find_last_trading_day(current_date, current_time):
    trade_date_list, cal_date = Tushare.tushare.get_trade_date_list()

    if cal_date is None or cal_date.empty:
        return current_date

    # 确保列名存在
    if 'cal_date' not in cal_date.columns or 'pretrade_date' not in cal_date.columns:
        print("Error: Required columns are missing from cal_date")
        return current_date

    # 获取最新交易日和前一个交易日
    lastDate = cal_date['cal_date'].iloc[0]
    pre_date = cal_date['pretrade_date'].iloc[0]
    # 判断类型后调用函数
    if isinstance(lastDate, str):
        lastDate = format_date(lastDate)

    if isinstance(pre_date, str):
        pre_date = format_date(pre_date)

    # 如果最后一个交易日早于当前日期，直接返回最后一个交易日
    if lastDate < current_date:
        return lastDate

    # 根据市场是否收市时间决定返回值
    if is_market_close_time(current_time):
        return current_date
    else:
        return pre_date


def getTest():
    query = "SELECT * FROM trade_cal"
    return db_connect.get_data_mp_alwaysup(query)


def execute_query(query: str, params: tuple) -> List[tuple]:
    """
    执行数据库查询并返回结果。
    """
    try:
        # 执行查询并返回结果
        result = db_connect.get_data_mp_alwaysup(query, params)

        if not result or len(result) == 0:
            print("No data returned from the query.")
            return []

        return result
    except Exception as e:
        print(f"Database query failed: {e}")
        return []


def generate_query(
    table_name: str,
    field_names: List[str],
    conditions: Optional[Dict[str, str]] = None,
    order_by: Optional[str] = None,
    limit: Optional[int] = None,
    join_clause: Optional[str] = None,
    group_by: Optional[List[str]] = None,
    having_clause: Optional[str] = None
) -> str:
    """
    生成灵活的 SQL 查询语句。

    参数：
    - table_name: 表名
    - field_names: 字段名列表
    - conditions: 查询条件字典（可选），例如 {"ts_code": "000001.SZ", "trade_date": "2021-09-01"}
    - order_by: 排序字段（可选），例如 "trade_date DESC"
    - limit: 查询结果限制（可选），例如 100
    - join_clause: JOIN 子句（可选），例如 "INNER JOIN another_table ON table_name.id = another_table.foreign_id"
    - group_by: GROUP BY 字段列表（可选）
    - having_clause: HAVING 子句（可选）

    返回：
    - 生成的 SQL 查询语句字符串
      - 例句
    query = generate_query(
    table_name="stocks",
    field_names=["ts_code", "trade_date", "open", "close"],
    conditions={"ts_code": "000001.SZ"},
    order_by="trade_date DESC",
    limit=10
    )
    query = generate_query(
    table_name="stocks",
    field_names=["ts_code", "trade_date", "open", "close"],
    conditions={"ts_code": "000001.SZ"},
    order_by="trade_date DESC",
    limit=10
    )
    query = generate_query(
    table_name="stocks",
    field_names=["ts_code", "COUNT(*) AS trade_count"],
    join_clause="INNER JOIN trades ON stocks.ts_code = trades.ts_code",
    group_by=["stocks.ts_code"],
    having_clause="trade_count > 10"
    )
    """
    # 将字段名列表转换为逗号分隔的字符串
    fields_str = ", ".join(field_names)

    # 开始生成查询语句
    query = f"SELECT {fields_str} FROM `{table_name}`"

    # 添加 JOIN 子句
    if join_clause:
        query += f" {join_clause}"

    # 添加 WHERE 子句
    # Add WHERE clause if conditions are provided
    if conditions:
        conditions_parts = []
        for key, value in conditions.items():
            # Check if the value contains an operator
            if any(op in value for op in ["=", ">", "<", "!", "LIKE", "BETWEEN"]):
                conditions_parts.append(f"`{key}` {value}")
            else:
                # Assuming no operator provided in the string
                conditions_parts.append(f"`{key}`=%s")
        conditions_str = " AND ".join(conditions_parts)
        query += f" WHERE {conditions_str}"

    # 添加 GROUP BY 子句
    if group_by:
        group_by_str = ", ".join([f"`{col}`" for col in group_by])
        query += f" GROUP BY {group_by_str}"

    # 添加 HAVING 子句
    if having_clause:
        query += f" HAVING {having_clause}"

    # 添加 ORDER BY 子句
    if order_by:
        query += f" ORDER BY {order_by}"

    # 添加 LIMIT 子句
    if limit is not None:
        query += f" LIMIT {limit}"

    return query


def resample_data(df: pd.DataFrame, period: Literal['W', 'M'], field_names: List[str]) -> pd.DataFrame:
    # 确保 'trade_date' 列存在于 DataFrame 中
    if 'trade_date' not in df.columns:
        raise KeyError("'trade_date' column not found in DataFrame")

    # 确保 'trade_date' 是 datetime 类型
    df['trade_date'] = pd.to_datetime(df['trade_date'], errors='coerce')

    # 移除无法转换为 datetime 的行
    df = df.dropna(subset=['trade_date'])

    # 设置 'trade_date' 为索引
    df.set_index('trade_date', inplace=True)

    # 定义重采样周期
    resample_period = period  # 'W' for weekly, 'M' for monthly

    # 创建一个空的字典用于存储重采样数据
    resampled_data = {}
    field_names.remove('trade_date')
    # 对每个字段进行重采样
    for field in field_names:
        if field in df.columns:
            resampled_data[field] = df[field].resample(resample_period).mean().dropna().tail(250).round(2)
        else:
            raise KeyError(f"'{field}' column not found in DataFrame")

    # 将重采样数据字典转换为 DataFrame
    resampled_df = pd.DataFrame(resampled_data)

    # 重置索引以保持与原始数据结构一致
    resampled_df.reset_index(inplace=True)

    # 格式化日期列为 'YYYY-MM-DD'
    resampled_df['trade_date'] = resampled_df['trade_date'].dt.strftime('%Y-%m-%d')
    return resampled_df


def format_result_data(df: pd.DataFrame, columns: List[str]) -> Dict[str, Any]:
    """
    将 DataFrame 格式化为字典，适合 JSON 返回，并根据指定的列进行处理。

    参数:
    - df: pd.DataFrame - 需要格式化的 DataFrame
    - columns: List[str] - 需要包含在结果中的列名
    - date_format: str - 日期格式，默认为 '%Y-%m-%d'

    返回:
    - Dict[str, Any]: 格式化后的字典数据
    """
    # 格式化日期列
    result_data = {
        'trade_date': df['trade_date'].tail(250).tolist(),
    }
    # 确保列名没有额外空格
    df.columns = [col.strip() for col in df.columns]
    # 动态处理指定的列
    for column in columns:
        result_data[column] = df[column].tail(250).tolist()

    return result_data


def getLine_daily_data(
    period: str,
    tableName: str,
    columns: List[str],
    conditions: Optional[str] = None,
    order_by: Optional[str] = None,
    limit: Optional[int] = None,
    join_clause: Optional[str] = None,
    group_by: Optional[List[str]] = None,
    having_clause: Optional[str] = None
) -> Dict[str, Any]:
    """
    获取指定 ts_code 和 period 的市场金额数据，并支持灵活的 SQL 查询构建。

    参数:    
    - period: str - 数据周期（'W' 表示周，'M' 表示月）
    - table_name: str - 数据所在的表名
    - columns: List[str] - 需要包含在结果中的列名
    - conditions: Optional[Dict[str, str]] - 查询的条件字典
    - order_by: Optional[str] - 排序字段
    - limit: Optional[int] - 查询结果限制
    - join_clause: Optional[str] - SQL JOIN 子句
    - group_by: Optional[List[str]] - GROUP BY 子句
    - having_clause: Optional[str] - HAVING 子句

    返回:
    - Dict[str, Any]: JSON 格式的返回数据，包含指定的列和格式化的日期
    """

    # table_name = "market_amount"  # 假设你的表名是这样，可以根据实际情况调整
    field_names = ["trade_date"] + columns  # 默认包含 trade_date 和用户指定的列

    # conditions = {'ts_code': 'HS_MARKET'}
    # 生成 SQL 查询语句
    query = generate_query(
        table_name=tableName,
        field_names=field_names,
        conditions=conditions,
        order_by=order_by,
        limit=limit,
        join_clause=join_clause,
        group_by=group_by,
        having_clause=having_clause
    )

    # 确保 conditions 为非空且包含有效参数
    condition_values = tuple(conditions.values()) if conditions else ()
    # print(condition_values)
    # 执行查询，获取结果
    result = execute_query(query, condition_values)  # 假设 tsCode 是查询条件之一

    # 将查询结果转换为 DataFrame 以便后续处理
    df = pd.DataFrame(result, columns=field_names)

    # 根据 period 进行数据重采样（如果需要）
    if period in ['W', 'M']:
        df = resample_data(df, period, field_names)

    # 格式化结果数据
    result_data = format_result_data(df, columns=field_names)

    return result_data


# 本地数据缓存json文件名
filename = 'dataTypes/localData.json'
# 可以把每天需要更新的固定数据用crontab定时任务保存到本地json文件
# 定时任务单独编写代码


def load_data():
    """从文件加载数据"""
    if os.path.exists(filename):
        with open(filename, 'r') as file:
            return json.load(file)
    return {}


def is_trade_date():
    jsonFile = load_data()    
    return jsonFile['realMarket']['is_tradeDate']


def is_market_open():
    current_time = datetime.now()
    return (is_trade_date() and  # 先检查是否为交易日
            (current_time.hour == 9 and current_time.minute >= 30) or
            (current_time.hour == 10) or
            (current_time.hour == 11 and current_time.minute < 30) or
            (current_time.hour == 13) or
            (current_time.hour == 14) or
            (current_time.hour == 15 and current_time.minute == 0))


def read_hdf_data(file_path: str, key: str) -> pd.DataFrame:
    """从指定的 HDF 文件中读取数据"""
    try:
        with pd.HDFStore(file_path, mode='r') as store:
            if key in store.keys():
                df = store[key]
                return df
            else:
                print(f"Key '{key}' not found in the HDF file.")
                return pd.DataFrame()
    except Exception as e:
        print(f"Error while reading HDF file: {e}")
        return pd.DataFrame()
# print(is_tradeDate())
# now, today = datetime.now(), datetime.now().date()
# close_date = find_last_trading_day(today, now)
# print(close_date)
